set doc off
set verify off
--
--    Program: CAPGLEXT_RATE_HIST.sql
--    Description:
--    Extract historical rate associated with the sob that the user specified 
--
--    Revision:
--    1st     
--    Parameter: 1. set of books short name e.g. SOB_C999


--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare   
  l_processed_count_1 	number;
  l_rejected_count_1  	number;
  l_processed_count_2	number;
  l_rejected_count_2	   number;
  l_err_msg       		varchar2(2000);
  
  l_utl_file_1  	   utl_file.file_type;
  l_utl_file_2 		utl_file.file_type;
  l_filename_1 		varchar2(255);
  l_filename_2 		varchar2(255);
  l_pathname  		   varchar2(100);

  l_ngo_sob			   varchar2(20);
 
  cursor cur_h (p_ngo_sob varchar2) is
    select * from gl_historical_rates 
    where set_of_books_id = (select set_of_books_id 
                             from gl_sets_of_books 
                             where upper(short_name) = upper(p_ngo_sob));
  
  cursor cur_hr (p_ngo_sob varchar2) is
    select * from gl_historical_rate_ranges 
    where set_of_books_id = (select set_of_books_id 
                             from gl_sets_of_books 
                             where upper(short_name) = upper(p_ngo_sob));
                             
begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPGL_RATE_HIST.dat';
  l_filename_2 := 'CAPGL_RATE_HIST_RANGE.dat';
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);
  
  l_processed_count_1 := 0;
  l_rejected_count_1  := 0;
  l_processed_count_2 := 0;
  l_rejected_count_2  := 0;
                                           
  l_ngo_sob := '&1';
  
  for h in cur_h(l_ngo_sob) loop
        
    utl_file.put_line( l_utl_file_1, 
      h.SET_OF_BOOKS_ID      ||chr(9)||
      h.PERIOD_NAME          ||chr(9)||
      h.PERIOD_NUM           ||chr(9)||
      h.PERIOD_YEAR          ||chr(9)||
      h.CODE_COMBINATION_ID  ||chr(9)||
      h.TARGET_CURRENCY      ||chr(9)||
      h.UPDATE_FLAG          ||chr(9)||
      h.USAGE_CODE           ||chr(9)||
      to_char(h.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
      h.LAST_UPDATED_BY      ||chr(9)||
      to_char(h.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
      h.CREATED_BY           ||chr(9)||
      h.LAST_UPDATE_LOGIN    ||chr(9)||
      h.RATE_TYPE            ||chr(9)||
      h.TRANSLATED_RATE      ||chr(9)||
      h.TRANSLATED_AMOUNT    ||chr(9)||
      h.ACCOUNT_TYPE         ||chr(9)||
      h.ATTRIBUTE1           ||chr(9)||
      h.ATTRIBUTE2           ||chr(9)||
      h.ATTRIBUTE3           ||chr(9)||
      h.ATTRIBUTE4           ||chr(9)||
      h.ATTRIBUTE5           ||chr(9)||
      h.CONTEXT              ||chr(9)||
      CAPBREXT_COMMON.GetUser(h.LAST_UPDATED_BY)|| chr(9) ||
      CAPBREXT_COMMON.GetUser(h.CREATED_BY)|| chr(9) ||
      CAPBREXT_COMMON.GetSOBShortName(h.SET_OF_BOOKS_ID)|| chr(9) ||
      CAPBREXT_COMMON.GetSegment(h.CODE_COMBINATION_ID)
    );
    
    l_processed_count_1 := l_processed_count_1 + 1; 
        
    for hr in cur_hr (l_ngo_sob) loop    
          
      utl_file.put_line( l_utl_file_2, 
        hr.HISTORICAL_RANGE_ID  ||chr(9)||
        hr.SET_OF_BOOKS_ID      ||chr(9)||
        hr.TARGET_CURRENCY      ||chr(9)||
        hr.PERIOD_NAME          ||chr(9)||
        hr.LAST_UPDATE_DATE     ||chr(9)||
        hr.LAST_UPDATED_BY      ||chr(9)||
        hr.TRANSLATED_RATE      ||chr(9)||
        hr.TRANSLATED_AMOUNT    ||chr(9)||
        hr.STATUS               ||chr(9)||
        hr.CREATION_DATE        ||chr(9)||
        hr.CREATED_BY           ||chr(9)||
        hr.LAST_UPDATE_LOGIN    ||chr(9)||
        hr.SEGMENT1_LOW         ||chr(9)||
        hr.SEGMENT1_HIGH        ||chr(9)||
        hr.SEGMENT2_LOW         ||chr(9)||
        hr.SEGMENT2_HIGH        ||chr(9)||
        hr.SEGMENT3_LOW         ||chr(9)||
        hr.SEGMENT3_HIGH        ||chr(9)||
        hr.SEGMENT4_LOW         ||chr(9)||
        hr.SEGMENT4_HIGH        ||chr(9)||
        hr.SEGMENT5_LOW         ||chr(9)||
        hr.SEGMENT5_HIGH        ||chr(9)||
        hr.SEGMENT6_LOW         ||chr(9)||
        hr.SEGMENT6_HIGH        ||chr(9)||
        hr.SEGMENT7_LOW         ||chr(9)||
        hr.SEGMENT7_HIGH        ||chr(9)||
        hr.SEGMENT8_LOW         ||chr(9)||
        hr.SEGMENT8_HIGH        ||chr(9)||
        hr.SEGMENT9_LOW         ||chr(9)||
        hr.SEGMENT9_HIGH        ||chr(9)||
        hr.SEGMENT10_LOW        ||chr(9)||
        hr.SEGMENT10_HIGH       ||chr(9)||
        hr.SEGMENT11_LOW        ||chr(9)||
        hr.SEGMENT11_HIGH       ||chr(9)||
        hr.SEGMENT12_LOW        ||chr(9)||
        hr.SEGMENT12_HIGH       ||chr(9)||
        hr.SEGMENT13_LOW        ||chr(9)||
        hr.SEGMENT13_HIGH       ||chr(9)||
        hr.SEGMENT14_LOW        ||chr(9)||
        hr.SEGMENT14_HIGH       ||chr(9)||
        hr.SEGMENT15_LOW        ||chr(9)||
        hr.SEGMENT15_HIGH       ||chr(9)||
        hr.SEGMENT16_LOW        ||chr(9)||
        hr.SEGMENT16_HIGH       ||chr(9)||
        hr.SEGMENT17_LOW        ||chr(9)||
        hr.SEGMENT17_HIGH       ||chr(9)||
        hr.SEGMENT18_LOW        ||chr(9)||
        hr.SEGMENT18_HIGH       ||chr(9)||
        hr.SEGMENT19_LOW        ||chr(9)||
        hr.SEGMENT19_HIGH       ||chr(9)||
        hr.SEGMENT20_LOW        ||chr(9)||
        hr.SEGMENT20_HIGH       ||chr(9)||
        hr.SEGMENT21_LOW        ||chr(9)||
        hr.SEGMENT21_HIGH       ||chr(9)||
        hr.SEGMENT22_LOW        ||chr(9)||
        hr.SEGMENT22_HIGH       ||chr(9)||
        hr.SEGMENT23_LOW        ||chr(9)||
        hr.SEGMENT23_HIGH       ||chr(9)||
        hr.SEGMENT24_LOW        ||chr(9)||
        hr.SEGMENT24_HIGH       ||chr(9)||
        hr.SEGMENT25_LOW        ||chr(9)||
        hr.SEGMENT25_HIGH       ||chr(9)||
        hr.SEGMENT26_LOW        ||chr(9)||
        hr.SEGMENT26_HIGH       ||chr(9)||
        hr.SEGMENT27_LOW        ||chr(9)||
        hr.SEGMENT27_HIGH       ||chr(9)||
        hr.SEGMENT28_LOW        ||chr(9)||
        hr.SEGMENT28_HIGH       ||chr(9)||
        hr.SEGMENT29_LOW        ||chr(9)||
        hr.SEGMENT29_HIGH       ||chr(9)||
        hr.SEGMENT30_LOW        ||chr(9)||
        hr.SEGMENT30_HIGH       ||chr(9)||
        CAPBREXT_COMMON.GetUser(hr.LAST_UPDATED_BY)|| chr(9) ||
        CAPBREXT_COMMON.GetUser(hr.CREATED_BY)|| chr(9) ||
        CAPBREXT_COMMON.GetSOBShortName(hr.SET_OF_BOOKS_ID)  
      );
    
      l_processed_count_2 := l_processed_count_2 + 1;      
    
    end loop;       
                      
  end loop;
  
  l_err_msg := ''; 
  if l_processed_count_1 = 0 then
    l_rejected_count_1 := l_rejected_count_1 + 1;
    l_err_msg := 'No Historical Rate associated with the SOB '||l_ngo_sob;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPGL_RATE_HIST', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
  
  l_err_msg := ''; 
  if l_processed_count_2 = 0 then
    l_rejected_count_2 := l_rejected_count_2 + 1;
    l_err_msg := 'No Historical Rate Range associated with the SOB '||l_ngo_sob;
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPGLEXT_RATE_HIST_RANGE', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
          
  commit;
  
  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
